const mysql = require('mysql')

// var connection = mysql.createConnection({
//   host: '49.232.9.254',
//   port: 3306,      
//   user: 'root',
//   password: '17729014809@asdZXC',
//   database: 'sylvie_galcg'
// })
 
// connection.connect((err) => {
//     if (err) {
//         console.log("连接失败")
//         console.log(err)
//     }
// })

let connection
let timer


function handleError () {
    connection = null
    connection = mysql.createConnection({
        host: '49.232.9.254',
        port: 3306,      
        user: 'root',
        password: '17729014809@asdZXC',
        database: 'sylvie_galcg'
    })

    //连接错误，2秒重试
    connection.connect(function (err) {
        if (err) {
            console.log('error when connecting to db:', err)
            setTimeout(handleError , 2000)
        } else {
            timer = setInterval(() => {
                console.log('ping...');
                connection.ping((err) => {
                    if (err) {
                        console.log('ping error: ' + JSON.stringify(err))
                        clearInterval(timer)
                        setTimeout(handleError , 2000)
                    }
                })
            }, 1800000)
        }
    })

    connection.on('error', function (err) {
        console.log('db error', err)
        // 如果是连接断开，自动重新连接
        if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            handleError()
        } else {
            throw err
        }
    })
}


handleError()


function queryArticleForTop() {
    return new Promise((resolve, reject) => {
        connection.query('select * from articles where classify=?',["top"], function (error, results, fields) {
            if (error) {
                reject(error)
            } else {
                resolve(results)
            }
        })
    })
}

function queryArticleForNew() {
    return new Promise((resolve, reject) => {
        connection.query('select * from articles where classify=?',["new"], function (error, results, fields) {
            if (error) {
                reject(error)
            } else {
                resolve(results)
            }
        })
    })
}

function queryArticleForAcg() {
    return new Promise((resolve, reject) => {
        connection.query('select * from articles where classify=?',["acg"], function (error, results, fields) {
            if (error) {
                reject(error)
            } else {
                resolve(results)
            }
        })
    })
}

function queryArticleForGame() {
    return new Promise((resolve, reject) => {
        connection.query('select * from articles where classify=?',["game"], function (error, results, fields) {
            if (error) {
                reject(error)
            } else {
                resolve(results)
            }
        })
    })
}

function queryArticle(id) {
    return new Promise((resolve, reject) => {
        connection.query('select * from articles where article=?', [id], function (error, results, fields) {
            if (error) {
                reject(error)
            } else {
                resolve(results)
            }
        })
    })    
} 

function queryTopic() {
    return new Promise((resolve, reject) => {
        connection.query('select * from topics', function (error, results, fields) {
            if (error) {
                reject(error)
            } else {
                resolve(results)
            }
        })
    })    
} 

module.exports = {
    queryArticle,
    queryArticleForTop,
    queryArticleForNew,
    queryArticleForAcg,
    queryArticleForGame,
    queryTopic
}